package com.sinosoft.dsp.platform.services;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.NoneScoped;

import com.sinosoft.builder.templates.concept.AttributeElementType;
import com.sinosoft.builder.templates.concept.BusinessConcept;
import com.sinosoft.builder.templates.concept.ComparisonOperatorType;
import com.sinosoft.builder.templates.concept.CreateDefaultValueType;
import com.sinosoft.builder.templates.concept.CreateFunctionExpressionType;
import com.sinosoft.builder.templates.concept.FromElementType;
import com.sinosoft.builder.templates.concept.FromType;
import com.sinosoft.builder.templates.concept.JoinElementType;
import com.sinosoft.builder.templates.concept.LogicalOperatorType;
import com.sinosoft.builder.templates.concept.MethodType;
import com.sinosoft.builder.templates.concept.OrderByElementType;
import com.sinosoft.builder.templates.concept.SearchForm;
import com.sinosoft.builder.templates.concept.SearchType;
import com.sinosoft.builder.templates.concept.SelectElementType;
import com.sinosoft.builder.templates.concept.SqlConfigType;
import com.sinosoft.builder.templates.concept.WhereElementType;
import com.sinosoft.dsp.platform.components.portal.input.elements.ISinoRangeTagElement;
import com.sinosoft.dsp.platform.components.portal.input.elements.ISinoTagElement;
import com.sinosoft.dsp.platform.exception.SinoPlatformException;
import com.sinosoft.dsp.platform.manager.ApplicationBuilder;
import com.sinosoft.dsp.platform.utils.SinoManagedBean;
import com.sinosoft.dsp.system.core.log.Log;
import com.sinosoft.dsp.system.utils.Const;

/**
 * 查询表单服务
 * 
 * @author 潘巍（PeterPan）
 * @since 2011-6-18 下午10:45:48
 * 
 */
@ManagedBean(name = "searchFormService")
@NoneScoped
public class SearchFormService {
	private static final Log log = Log
			.getLog(SearchFormService.class.getName());

	public static final String SQL_SELECT = "SELECT ";

	public static final String SQL_FROM = " FROM ";

	public static final String SQL_FROM_OUTER = " LEFT ";

	public static final String SQL_FROM_JOIN = " JOIN ";

	public static final String SQL_FROM_FETCH = " FETCH ";

	public static final String SQL_WHERE = " WHERE ";

	public static final String SQL_ORDERBY = " ORDER BY ";

	public static final String SQL_GROUPBY = " GROUP BY ";

	public static final String SQL_HAVING = " HAVING ";

	public static final String SQL_COUNT = " COUNT";

	public static final String SQL_DESC = " DESC ";

	/**
	 * 初始化查询表单
	 */
	private void initSearchForm() {
		Map<String, SearchForm> form = new HashMap<String, SearchForm>();
		ApplicationBuilder ab = SinoManagedBean.findBean("applicationBuilder");
		BusinessConcept bc = ab.getConcept();
		if (bc.getSearchFormConcept() != null
				&& bc.getSearchFormConcept().getSearchForm() != null) {
			for (SearchForm sf : bc.getSearchFormConcept().getSearchForm()) {
				form.put(sf.getId(), sf);
			}
		}
		ab.setSearchForms(form);
	}

	/**
	 * 通过查询表单ID获得查询表单对象
	 * 
	 * @param id
	 * @return
	 */
	public SearchForm getSearchFormById(String id) {
		try {
			ApplicationBuilder ab = SinoManagedBean.findBean("applicationBuilder");
			if (ab.getSearchForms() == null) {
				initSearchForm();
			}
			return ab.getSearchForms().get(id);
		} catch (Exception e) {
			log.error("通过查询表单ID获得查询表单对象出现错误(getSearchFormById)！");
		}
		return null;
	}

	/**
	 * 通过查询表单查询的元数据和对应值，获得SQL
	 * 
	 * @param meta
	 * @param fields
	 * @return
	 * @throws SinoPlatformException
	 */
	public String getSQLForSearchForm(SearchType search,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		if (search != null) {
			return getSQLForSearchForm(search.getSqlConfig(), fields);
		}
		return null;
	}

	public String getSQLForSearchForm(SqlConfigType sqlConfigType,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		if (sqlConfigType != null) {
			StringBuffer sql = new StringBuffer();

			// SELECT
			sql.append(SQL_SELECT);
			if (Const.isEmpty(sqlConfigType.getSelect().getSegment())) {
				sql.append(getSQLSelect(sqlConfigType.getSelect()
						.getSelectElement(), fields));
			} else {
				sql.append(sqlConfigType.getSelect().getSegment());
			}

			// 固定部分
			getSQLContantForSearchForm(sql, sqlConfigType, fields);

			return sql.toString();
		}
		return null;
	}

	public String getSQLCountForSearchForm(SqlConfigType sqlConfigType,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		if (sqlConfigType != null) {
			StringBuffer sql = new StringBuffer();

			SelectElementType p = (SelectElementType) sqlConfigType.getSelect()
					.getCountParameter();
			if (p == null) {
				p = sqlConfigType.getSelect().getSelectElement().get(0);
			}

			// SELECT
			sql.append(SQL_SELECT);
			sql.append(SQL_COUNT + "(");

			appendParameter(sql, p, fields);

			sql.append(")");

			// 固定部分
			getSQLContantForSearchForm(sql, sqlConfigType, fields);

			return sql.toString();
		}
		return null;
	}

	private void getSQLContantForSearchForm(StringBuffer sql,
			SqlConfigType sqlConfigType, Map<String, ISinoTagElement> fields)
			throws SinoPlatformException {
		// FROM
		sql.append(SQL_FROM);
		sql.append(getSQLFrom(sqlConfigType.getFrom()));

		// WHERE
		String whereSql;
		if (Const.isEmpty(sqlConfigType.getWhere().getSegment())) {
			whereSql = getSQLWhere(sqlConfigType.getWhere().getWhereElement(),
					fields);
		} else {
			whereSql = sqlConfigType.getWhere().getSegment();
		}
		if (!Const.isEmpty(whereSql)) {
			sql.append(SQL_WHERE);
			sql.append(whereSql);
		}

		// ORDER BY
		String orderby = null;
		if (sqlConfigType.getOrderBy() != null) {
			if (Const.isEmpty(sqlConfigType.getOrderBy().getSegment())) {
				orderby = getSQLOrderBy(sqlConfigType.getOrderBy()
						.getOrderByElement(), fields);
			} else {
				orderby = sqlConfigType.getOrderBy().getSegment();
			}
			if (!Const.isEmpty(orderby)) {
				sql.append(SQL_ORDERBY);
				sql.append(orderby);
			}
		}

		if (Const.isEmpty(orderby)) {
			String groupby = null;
			if (sqlConfigType.getGroupBy() != null) {
				if (Const.isEmpty(sqlConfigType.getGroupBy().getSegment())) {
					groupby = getSQLGroupBy(sqlConfigType.getGroupBy()
							.getGroupByElement(), fields);
				} else {
					groupby = sqlConfigType.getGroupBy().getSegment();
				}
				if (!Const.isEmpty(groupby)) {
					sql.append(SQL_GROUPBY);
					sql.append(groupby);
				}
			}

			if (!Const.isEmpty(groupby)) {
				String having;
				if (sqlConfigType.getHaving() != null) {
					if (Const.isEmpty(sqlConfigType.getHaving().getSegment())) {
						having = getSQLHaving(sqlConfigType.getHaving()
								.getHavingElement(), fields);
					} else {
						having = sqlConfigType.getHaving().getSegment();
					}
					if (!Const.isEmpty(having)) {
						sql.append(SQL_HAVING);
						sql.append(having);
					}
				}
			}
		}
	}

	private String getSQLSelect(List<SelectElementType> se,
			Map<String, ISinoTagElement> fields) {
		StringBuffer selectSql = new StringBuffer();
		if (se != null) {
			for (SelectElementType set : se) {
				dealSelectElement(selectSql, set, fields);
			}
		}
		return selectSql.toString();
	}

	private void dealSelectElement(StringBuffer selectSql,
			SelectElementType set, Map<String, ISinoTagElement> fields) {
		if (selectSql.length() > 0) {
			selectSql.append(",");
		}
		if (set.getAggregateFunction() != null) {
			selectSql.append(set.getAggregateFunction().value()).append("(");
		}
		appendParameter(selectSql, set, fields);
		if (set.getAggregateFunction() != null) {
			selectSql.append(")");
		}
	}

	private String getSQLFrom(FromType ft) {
		if (Const.isEmpty(ft.getSegment())) {
			StringBuffer fromSql = new StringBuffer();
			if (ft.getFromElement() != null) {
				StringBuffer fromSegment = new StringBuffer();
				for (FromElementType fet : ft.getFromElement()) {
					fromSegment.setLength(0);
					fromSegment.append(" ").append(fet.getTableClass()).append(
							" ").append(fet.getAlias());
					if (fet.getJoinElement() != null) {
						for (JoinElementType jet : fet.getJoinElement()) {
							if (jet.isOuter()) {
								fromSegment.append(SQL_FROM_OUTER);
							}
							fromSegment.append(SQL_FROM_JOIN);
							if (jet.isFetch()) {
								fromSegment.append(SQL_FROM_FETCH);
							}
							fromSegment.append(
									getParameterString(jet.getTabelRef(), jet
											.getField())).append(" ").append(
									jet.getAlias());
						}
					}

					if (fromSql.length() > 0) {
						fromSql.append(",");
					}
					fromSql.append(fromSegment.toString());
				}
			}
			return fromSql.toString();
		} else {
			return ft.getSegment();
		}
	}

	private String getSQLWhere(List<WhereElementType> wets,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		StringBuffer whereSql = new StringBuffer();
		if (wets != null) {
			StringBuffer whereSegment = new StringBuffer();
			for (WhereElementType wet : wets) {
				whereSegment.setLength(0);
				// NOT逻辑操作符
				if (wet.getLogicalOperator() == LogicalOperatorType.NOT) {
					String w = getSQLWhere(wet.getWhereElement(), fields);
					if (!Const.isEmpty(w)) {
						whereSegment.append(" NOT ").append(" ( ").append(w)
								.append(" ) ");

						whereSql.append(whereSegment);
					}
				}
				// 非NOT逻辑操作符
				else {
					// 子元素
					if (wet.getWhereElement() != null
							&& wet.getWhereElement().size() > 0) {
						String w = getSQLWhere(wet.getWhereElement(), fields);
						if (!Const.isEmpty(w)) {
							if (whereSql.length() > 0) {
								whereSql.append(" ").append(
										wet.getLogicalOperator().value())
										.append(" ");
							}

							whereSql.append(" ( ").append(w).append(" ) ");
						}
					}
					// 比较符-范围
					else if (wet.getComparisonOperator() == ComparisonOperatorType.BETWEEN) {
						// 左属性必须是参数类型，右属性必须是上下文值，否则抛出异常
						AttributeElementType left = wet.getLeftAttribute(), right = wet
								.getRightAttribute();
						if (left != null
								&& right != null
								&& left.getCreateParameter() != null
								&& right.getCreateDefaultValue() != null
								&& right.getCreateDefaultValue()
										.getMethodType() == MethodType.CONTEXT) {
							StringBuffer rightSb = new StringBuffer();
							boolean rtn = appendParameter(rightSb, right,
									fields);

							if (rtn) {
								ISinoTagElement obj = fields.get(right
										.getCreateDefaultValue().getName());
								if (obj instanceof ISinoRangeTagElement) {
									ISinoRangeTagElement range = (ISinoRangeTagElement) obj;

									// 下限
									if (range.hasLower()) {
										appendParameter(whereSegment, left,
												fields);
										if (range.isIncludeLower()) {
											whereSegment
													.append(ComparisonOperatorType.GREAT_EQUAL);
										} else {
											whereSegment
													.append(ComparisonOperatorType.GREAT);
										}
										whereSegment.append(rightSb);
										whereSegment.append("_min");
									}
									if (range.hasLower() && range.hasUpper()) {
										whereSegment.append(" "
												+ LogicalOperatorType.AND
														.value() + " ");
									}
									// 上限
									if (range.hasUpper()) {
										appendParameter(whereSegment, left,
												fields);
										if (range.isIncludeUpper()) {
											whereSegment
													.append(ComparisonOperatorType.LESS_EQUAL);
										} else {
											whereSegment
													.append(ComparisonOperatorType.LESS);
										}
										whereSegment.append(rightSb);
										whereSegment.append("_max");
									}

									if (whereSql.length() > 0) {
										whereSql.append(" ").append(
												wet.getLogicalOperator()
														.value()).append(" ");
									}

									whereSql.append(whereSegment);
								}
							}
						} else {
							throw new SinoPlatformException(
									"创建范围类型Where条件出现错误！请检查左属性和右属性是否同时存在，并且左属性必须是参数类型，右属性必须是上下文值。");
						}
					}
					// 比较符-IN或者NOT IN
					else if (wet.getComparisonOperator() == ComparisonOperatorType.IN
							|| wet.getComparisonOperator() == ComparisonOperatorType.NOT_IN) {
						// TODO
					}
					// 比较符-EXISTS或者NOT EXISTS
					else if (wet.getComparisonOperator() == ComparisonOperatorType.EXISTS
							|| wet.getComparisonOperator() == ComparisonOperatorType.NOT_EXISTS) {
						// TODO
					}
					// 其他比较符
					else {
						if (wet.getComparisonOperator() == null) {
							throw new SinoPlatformException("操作符不能为空，错误出现在"
									+ whereSql.toString() + "附近。");
						}

						// 左部
						boolean rtn = appendParameter(whereSegment, wet
								.getLeftAttribute(), fields);
						if (!rtn) {
							continue;
						}
						// 比较操作符
						whereSegment.append(" ").append(
								wet.getComparisonOperator().value())
								.append(" ");
						// 右部
						if (wet.getRightAttribute() != null) {
							rtn = appendParameter(whereSegment, wet
									.getRightAttribute(), fields);
							if (!rtn) {
								continue;
							}
						}

						if (whereSql.length() > 0) {
							whereSql.append(" ").append(
									wet.getLogicalOperator().value()).append(
									" ");
						}

						whereSql.append(whereSegment);
					}
				}
			}
		}
		return whereSql.toString();
	}

	private String getSQLOrderBy(List<OrderByElementType> et,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		StringBuffer sql = new StringBuffer();
		if (et != null) {
			for (OrderByElementType set : et) {
				if (sql.length() > 0) {
					sql.append(",");
				}
				appendParameter(sql, set, fields);
				if (!set.isAscending()) {
					sql.append(SQL_DESC);
				}
			}
		}
		return sql.toString();
	}

	private String getSQLGroupBy(List<SelectElementType> et,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		return getSQLSelect(et, fields);
	}

	private String getSQLHaving(List<WhereElementType> et,
			Map<String, ISinoTagElement> fields) throws SinoPlatformException {
		return getSQLWhere(et, fields);
	}

	/**
	 * 通过属性元素类型获得SQL参数值
	 * 
	 * @param sql
	 * @param type
	 * @param fields
	 * @return 表示fields中是否有对应的初始值(只对Where条件有用)
	 */
	private boolean appendParameter(StringBuffer sql,
			AttributeElementType type, Map<String, ISinoTagElement> fields) {
		if (type.getCreateDefaultValue() != null) {
			return getDefaultValueString(sql, type.getCreateDefaultValue(),
					fields);
		} else if (type.getCreateParameter() != null) {
			sql.append(getParameterString(type.getCreateParameter()
					.getTableRef(), type.getCreateParameter().getField()));
		} else if (type.getCreateFunctionExpression() != null) {
			return getCreateFunctionExpressionString(sql, type
					.getCreateFunctionExpression(), fields);
		}
		return true;
	}

	private String getParameterString(Object tableRef, String field) {
		String rtn = getFromAlias(tableRef);
		if (!Const.isEmpty(field)) {
			rtn += ".";
			rtn += field;
		}
		return rtn;
	}

	private String getFromAlias(Object tableRef) {
		if (tableRef instanceof FromElementType) {
			FromElementType t = (FromElementType) tableRef;
			return t.getAlias();
		} else if (tableRef instanceof JoinElementType) {
			JoinElementType t = (JoinElementType) tableRef;
			return t.getAlias();
		}
		return "";
	}

	private boolean getDefaultValueString(StringBuffer sql,
			CreateDefaultValueType type, Map<String, ISinoTagElement> fields) {
		if (type.getMethodType() == MethodType.ASSIGMENT) {
			sql.append(type.getValue());
		} else if (type.getMethodType() == MethodType.CONTEXT) {
			sql.append(":" + type.getName());
			return checkField(fields, type.getName());
		} else if (type.getMethodType() == MethodType.SESSION) {
			// TODO session中获得的都是String
		} else if (type.getMethodType() == MethodType.SYSTEM) {
			// TODO
		}

		return true;
	}

	private boolean getCreateFunctionExpressionString(StringBuffer sql,
			CreateFunctionExpressionType type,
			Map<String, ISinoTagElement> fields) {
		boolean rtn = true;
		sql.append(type.getFunctionExpression().value()).append("(");

		StringBuffer params = new StringBuffer();
		for (AttributeElementType attr : type.getFunctionParameters()) {
			if (params.length() > 0) {
				params.append(",");
			}
			if (!appendParameter(params, attr, fields)) {
				rtn = false;
			}
		}

		sql.append(")");
		return rtn;
	}

	private boolean checkField(Map<String, ISinoTagElement> fields, String name) {
		ISinoTagElement f = fields.get(name);
		if (f != null) {
			return !f.isEmpty();
		}
		return false;
	}
}
